The Data Diagrammer in Designer/2000

By David C. Moss

In our series of articles on Designer/2000, we've explored the use of logical and physical models to define software applications. A complete logical model consists of all the things your application will need to do and the information needed to do them. A complete physical model shows how to implement the logical model, including relational table definitions and software module specifications.

Last month, we began using the Data Diagrammer in Designer/2000 to examine and modify table, column, primary key, and foreign key definitions that we generated using the Database Design Wizard. In this article, we'll continue refining those definitions using the Data Diagrammer.

Getting there

To begin, you'll need to navigate to the Data Diagrammer. As we pointed out in our last article, you can use the Data Diagrammer icon directly from Windows, or you can first click on the Designer/2000 Repository Object Navigator (RON) in Windows, then pull down on the Tools menu and select Data Diagrammer. For this article, we'll assume you've already created some table definitions and have a basic data diagram to refine.

In a data diagram, each box on the screen represents a table, and each line represents a foreign key. To edit table information, select a box by double-clicking on it. You'll see the Edit Table screen, depicted in Figure A, which contains separate tabs for editing Table, Column Definition, Column Display, Table Constraints, and Table Validation information. For the remainder of this article, we'll focus on refining this information.

Plurality

The Table tab shown in Figure A contains a number of fields into which you can enter data.

Figure A: You enter physical details about a table in the Edit Table screen.

The Name field may look like a no-brainer, but this can be deceptive. Typically, the name of a table is the plural of the entity name - the entity Customer becomes the table Customers. (You'll recall that an entity represents one occurrence of something, and a table represents many occurrences, hence the transition from singular to plural as we move from logical to physical design.) But what if you had a table with a long name, such as Airplane Parts Assembly Plan? You could certainly make it plural, as in Airplane_Parts_Assembly_Plans. However, your organization may have a list of standard abbreviations to use when naming tables. Using abbreviations, your table name could end up as Arpln_Prts_Asmbly_Pln - cryptic, yet still understandable.

While we're on the subject of abbreviations, you might wonder why we don't simply abbreviate entity names, then pluralize them when we're defining tables. Although we've seen many organizations do this, we would strongly caution against it for one reason: Your users need to understand the entity names. You create the entity-relationship diagram primarily as a communications tool in system design, so using familiar and understandable names is crucial.

However, when it comes to naming tables, the audience is less the users and more the programming staff. That doesn't mean that programmers, who are constantly bombarded with abbreviations, are better suited to using cryptic names. We would still suggest caution in over-abbreviating table names. Programmers are humans, too. Really. So, what we recommend is that you use descriptive (and full) entity names, then start abbreviating when you name your tables.

Aliases

This brings us to the Alias field in the Edit Table screen. An alias is a unique short name used primarily as a prefix for index definitions. In SQL an alias is also used as an identifier for a table when columns from different tables have the same name. When Designer/2000 generates code for your applications, it will use the alias for this purpose.

So why, you might ask, don't we just use a column prefix for all the columns on a table? We could simply add this prefix to the Col. Prefix field, then all columns in that table would have a unique prefix. For example, for the Customers table, we could use CUST as a column prefix. The columns would thus be CUST_ NAME, CUST_ADDRESS, CUST_TELEPHONE, and so on. However, with four-character prefixes on every column name in every table in your application, that's a lot of extra typing when it comes to writing SQL. So, many database designers don't use the column prefix, preferring instead to use a table alias, when necessary, to distinguish columns with the same name from different tables.

The Owned? checkbox in the Edit Table screen shown in Figure A indicates whether the table is native to your application or is defined elsewhere. This field is not enterable in this screen.

The Display Title field, shown in Figure A with the entry Customers, is the default title you wish to use in forms and reports. You use the Database field to indicate the database where the table definition will eventually be stored. You typically don't fill in this field until just before generating Data Definition Language (DDL). The DB Type field in Figure A allows you to select Oracle DDL generation or more generic ANSI SQL.

A row is a row is a row

The Number of Rows fields are used to project how many rows the table will have initially and how many it will have when it's full. You can use this information to do database sizing. Typically, you'd collect this information for each entity during the Analysis stage.

Application developers often overlook the Locking Sequence field, but it's very important. You use it to designate a number indicating the order in which this table will be locked for insert, update, and delete across all modules in your application. This step helps ensure that deadlocks won't occur. For example, if Module A has locked the Customers table and is trying to lock the Operations table, while at the same time Module B has first locked the Operations table and is now trying to lock the Customers table, neither of the modules will be able to get the lock they need and a deadlock will occur. However, if both modules acquired table locks in the same order, Module A would lock the Customers table and then the Operations table, while Module B would wait for the Customers table to be released - with no deadlock.

You can set Locking Sequence information at any time prior to using Designer/2000 to generate DDL scripts and software modules.

If your application will be using some tables from another application, you'll need to set the Locking Sequence to synchronize locking in both applications. A frequent problem in implementation occurs when two different applications have chosen (or failed to choose) a locking sequence for tables, and the use of the two applications causes both applications to deadlock.

DB or not DB

The Storage Definition, Tablespace, Init Trans, Max Trans, Pct Free, and Pct Used fields are useful primarily to a database administrator (DBA) and are usually not even set in the initial creation of DDL scripts. Software developers are the primary users of early versions of the database, and there is usually considerable massaging of the database definitions as modules are completed and tested. As the database is sized and tuned, the DBA gathers more wisdom regarding the database layout and can make more educated decisions as to where and how tables should be stored for optimum effect.

Next time

In our next article, we'll continue to add detail to our table definition, addressing the Column Definition, Column Display, Constraints, and Validation tabs in the Edit Table screen.

Where to go for information

If you'd like to find out more about Oracle's approach to database design, please refer to Appendix F of CASE*Method: Entity-Relationship Modelling by Richard Barker (Oracle/Addison-Wesley, 1989). Also, two soon-to-be-published books on Designer/2000 are going to be must-haves. One book is by David Wendelken and Carrie Andersen. The other is by Chris and Maria Billings.

David Moss is a managing consultant with TrueNorth Consulting, Inc., an information technology consultancy with offices in Portland, Seattle, and San Jose. He has worked with Oracle's CASE tools since 1988, including over three years with Oracle Consulting Group. You can reach David by phone at (503) 220-1790 or by E-mail at truenrth@ix.netcom.com.

[The Cobb Group Home Page]

Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of Ziff-Davis Publishing Company.